package com.ipc.app.dal.dao.sql;

import java.sql.Time;
import java.util.Calendar;
import java.util.Date;

import org.apache.commons.lang.StringUtils;

/**
 * @author haijie.zhang
 * 
 */
public class InformationSQL {

	public static final String queryInformationCountByCity(String type,
			String city) {
		StringBuffer sql = new StringBuffer(
				"SELECT COUNT(information.INFORMATION_ID) FROM T_INFORMATION information LEFT JOIN T_ADDRESS address ON information.INFORMATION_ID = address.INFORMATION_ID");
		sql.append(" WHERE ");
		if (StringUtils.isNotBlank(type)) {
			sql.append("information.TYPE = ");
			sql.append("'");
			sql.append(type);
			sql.append("'");
			sql.append(" AND ");
		}
		sql.append("address.CITY = '");
		sql.append(city);
		sql.append("' AND ");
		sql.append("address.TYPE = '01'");
		return sql.toString();
	}

	public static final String getInformationDOByPagerAndCity(
			String informationType, String city) {
		StringBuffer sql = new StringBuffer(
				"SELECT information.INFORMATION_ID,information.MEMO,information.CREATE_TIME,information.MODIFY_TIME,information.DEPARTURE_TIME,information.SEAT_NUMBER,information.BOOKED_SEAT_NUMBER,information.PRICE,information.USER_ID,information.CAR_ID,information.TYPE,information.STATUS FROM T_INFORMATION information ");
		sql
				.append("LEFT JOIN T_ADDRESS address ON information.INFORMATION_ID = address.INFORMATION_ID ");
		sql.append(" WHERE ");
		if (StringUtils.isNotBlank(informationType)) {
			sql.append("information.TYPE = '");
			sql.append(informationType);
			sql.append("' AND ");
		}

		sql.append("address.CITY = '");
		sql.append(city);
		sql.append("' AND ");
		sql.append("address.TYPE = '01'");
		return sql.toString();
	}

	public static final String queryInformationCountBetween2Cities(
			String informationType, String departure, String destination) {
		StringBuffer sql = new StringBuffer(
				"SELECT COUNT(information.INFORMATION_ID)	FROM T_INFORMATION information");
		sql
				.append(" LEFT JOIN T_ADDRESS departure ON information.INFORMATION_ID = departure.INFORMATION_ID");
		sql
				.append(" LEFT JOIN T_ADDRESS destination ON information.INFORMATION_ID = destination.INFORMATION_ID");
		sql.append(" WHERE");

		if (StringUtils.isNotBlank(informationType)) {
			sql.append(" information.TYPE =");
			sql.append("'");
			sql.append(informationType);
			sql.append("'");
			sql.append(" AND");
		}

		sql.append(" departure.CITY = '");
		sql.append(departure);
		sql.append("' AND");

		sql.append(" destination.CITY = '");
		sql.append(destination);
		sql.append("' AND");

		sql.append(" departure.TYPE = '01'");
		sql.append(" AND");

		sql.append(" destination.TYPE = '03'");
		return sql.toString();
	}

	public static final String getInformationBetween2Cities(
			String informationType, String departure, String destination) {
		StringBuffer sql = new StringBuffer(
				"SELECT information.INFORMATION_ID,information.MEMO,information.CREATE_TIME,information.MODIFY_TIME,information.DEPARTURE_TIME,information.SEAT_NUMBER,information.BOOKED_SEAT_NUMBER,information.PRICE,information.USER_ID,information.CAR_ID,information.TYPE,information.STATUS FROM T_INFORMATION information");
		sql
				.append(" LEFT JOIN T_ADDRESS departure ON information.INFORMATION_ID = departure.INFORMATION_ID");
		sql
				.append(" LEFT JOIN T_ADDRESS destination ON information.INFORMATION_ID = destination.INFORMATION_ID");
		sql.append(" WHERE");

		if (StringUtils.isNotBlank(informationType)) {
			sql.append(" information.TYPE =");
			sql.append("'");
			sql.append(informationType);
			sql.append("'");
			sql.append(" AND");
		}

		sql.append(" departure.CITY = '");
		sql.append(departure);
		sql.append("' AND");

		sql.append(" destination.CITY = '");
		sql.append(destination);
		sql.append("' AND");

		sql.append(" departure.TYPE = '01'");
		sql.append(" AND");

		sql.append(" destination.TYPE = '03'");
		return sql.toString();
	}

	public static final String getRoundInformationCount(String informationType,
			Double startX, Double endX, Double startY, Double endY) {
		StringBuffer sql = new StringBuffer(
				"SELECT COUNT(information.INFORMATION_ID) FROM T_INFORMATION information LEFT JOIN T_ADDRESS departure ON information.INFORMATION_ID = departure.INFORMATION_ID");

		sql.append(" WHERE ");
		if (StringUtils.isNotBlank(informationType)) {
			sql.append("information.TYPE ='");
			sql.append(informationType);
			sql.append("' AND ");
		}
		sql.append("departure.X between ");
		sql.append(startX);
		sql.append(" AND ");
		sql.append(endX);

		sql.append(" AND ");

		sql.append("departure.Y between ");
		sql.append(startY);
		sql.append(" AND ");
		sql.append(endY);

		sql.append(" AND ");
		sql.append("departure.TYPE = '01'");

		return sql.toString();
	}

	public static final String getRoundInformation(String informationType,
			Double startX, Double endX, Double startY, Double endY) {
		StringBuffer sql = new StringBuffer(
				"SELECT information.INFORMATION_ID,information.MEMO,information.CREATE_TIME,information.MODIFY_TIME,information.DEPARTURE_TIME,information.SEAT_NUMBER,information.BOOKED_SEAT_NUMBER,information.PRICE,information.USER_ID,information.CAR_ID,information.TYPE,information.STATUS FROM T_INFORMATION information LEFT JOIN T_ADDRESS departure ON information.INFORMATION_ID = departure.INFORMATION_ID");

		sql.append(" WHERE ");
		if (StringUtils.isNotBlank(informationType)) {
			sql.append("information.TYPE ='");
			sql.append(informationType);
			sql.append("' AND ");
		}
		sql.append("departure.X between ");
		sql.append(startX);
		sql.append(" AND ");
		sql.append(endX);

		sql.append(" AND ");

		sql.append("departure.Y between ");
		sql.append(startY);
		sql.append(" AND ");
		sql.append(endY);

		sql.append(" AND ");
		sql.append("departure.TYPE = '01'");

		return sql.toString();
	}

	public static final String updateExpiredInformation(String status, Date date) {
		StringBuffer sql = new StringBuffer("UPDATE T_INFORMATION information ");
		sql.append("SET information.STATUS= '");
		sql.append(status);
		sql.append("'");
		sql.append("WHERE information.DEPARTURE_TIME < '");
		sql.append(date);
		sql.append("'");

		return sql.toString();

	}

}
